package cn.nawang.ebeim.client.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.nawang.ebeim.client.bean.BackupBean;
import cn.nawang.ebeim.client.bean.DataSourceVersion;
import cn.nawang.ebeim.client.bean.DbVersionTreeBean;
import cn.nawang.ebeim.client.bean.RecoveryBean;
import cn.nawang.ebeim.client.bean.TaskStatusBean;

public class DbUtil {
  public static final int STATUS_NEW = 0;
  public static final int STATUS_RUNNING = 1;
  public static final int STATUS_FINISHED = 2;
  public static final int STATUS_ERROR = 3;
  
  public static final int TASK_TYPE_FILE = 0;
  public static final int TASK_TYPE_VERSION = 1;
  public static final int TASK_STATUS_RUNNING = 0;
  public static final int TASK_STATUS_ENCRYPT = 1;
  public static final int TASK_STATUS_ZIP = 2;
  public static final int TASK_STATUS_SUCCESS = 3;
  public static final int TASK_STATUS_BREAK_POINT = 4;
  

  private static final String SQL_TABLE_BACKUP_TASK = "create table if not exists task_backup("
      + "id integer primary key autoincrement, dsId varchar(50), status integer)";
  private static final String SQL_TABLE_RECOVERY_TASK = "create table if not exists task_recovery("
      + "id integer primary key autoincrement, dsId varchar(50), path varchar(255), version varchar(20), status integer, recoveryPath varchar(255),nid varchar(50),userClickNodePath varchar(255))";
  private static final String SQL_TABLE_TASK_STATUS = "create table if not exists task_status("
      + "id integer primary key autoincrement, dsId varchar(50),signature varchar(100),type integer,"
      + " status integer,ds_path varchar(200),info_path varchar(200),info_name varchar(100),file_size number,"
      + " major_version integer,minor_version integer,ds_type integer,is_encrypt varchar(1))";
  private static final String SQL_TABLE_VERSION_TREE = "create table if not exists version_tree("
      + "id varchar(36) primary key, pid varchar(36),ds_id varchar(50),name varchar(255), status integer,version varchar(50))";
 
  private static final String SQL_INSERT_VERSION_TREE = "insert into version_tree(id,pid,ds_id,name,status,version) values(?, ?,?,?, ?,?)";
  private static final String SQL_INSERT_TASK_STATUS = "insert into task_status(dsId,signature,type,status,ds_path,info_path,info_name,file_size,major_version,minor_version,ds_type,is_encrypt) values(?,?, ?, ?, ?,?,?,?,?,?,?,?);";
  private static final String SQL_INSERT_BACKUP = "insert into task_backup(dsId,status) values(?, ?)";
  private static final String SQL_INSERT_RECOVERY =
      "insert into task_recovery(dsId, path, version, status, recoveryPath, nid,userClickNodePath) " + "values(?, ?, ?, ?, ?, ?, ?)";

  private static final String SQL_COUNT_TASK_STATUS_UNFINISHED =
      "select count(*) as count from task_status where type=? and dsId=? and status <> 3 order by id asc";
  
  private static final String SQL_SELECT_TASK_STATUS_UNFINISHED =
      "select id,dsId,signature,type,status,ds_path,info_path,info_name,file_size,major_version,minor_version,ds_type,is_encrypt"
      + " from task_status where dsId=? and status <> 3 order by id asc";
  private static final String SQL_SELECT_TASK_STATUS_BREAKPOINT =
      "select id,dsId,signature,type,status,ds_path,info_path,info_name,file_size,major_version,minor_version,ds_type,is_encrypt"
      + " from task_status where dsId=? and status = 4 order by id asc";
  private static final String SQL_SELECT_TASK_STATUS_FINISHED =
      "select id,dsId,signature,type,status,ds_path,info_path,info_name,file_size,major_version,minor_version,ds_type,is_encrypt"
      + " from task_status where type=? and dsId=? and status=3 order by id asc";
  private static final String SQL_SELECT_TASK_STATUS_FINISHED_FILESIZE_SUM =
      "select sum(file_size) as count from task_status where type=? and dsId=? and status=3";
  private static final String SQL_SELECT_BACKUP =
      "select * from task_backup where status=0 order by id asc";
  private static final String SQL_SELECT_RECOVERY =
      "select * from task_recovery where status=0 order by id asc";
  private static final String SQL_SELECT_RECOVERY_Running =
          "select * from task_recovery where status=1 order by id asc";
  
  private static final String SQL_SELECT_RECOVERY_ALL =
      "select * from task_recovery order by id desc";
  private static final String SQL_COUNT_RECOVERY_ALL =
      "select count(*) as count from task_recovery";
  private static final String SQL_SELECT_RECOVERY_PAGE =
      "select * from task_recovery order by id desc limit ? offset ?";
  private static final String SQL_SELECT_VERSION_TREE =
      "select * from version_tree where ds_id=? and pid=? and version=? order by id asc";
  
  private static final String SQL_UPDATE_BACKUP = "update task_backup set status=? where id=?";
  private static final String SQL_UPDATE_RECOVERY = "update task_recovery set status=? where id=?";
  private static final String SQL_UPDATE_TASK_STATUS_FILE = "update task_status set status=? where dsId=? and signature=? and type = 0";
  private static final String SQL_UPDATE_TASK_STATUS_VERSION = "update task_status set status=? where dsId=? and major_version=? and minor_version=? and type = 1";
  
  private static final String SQL_DELETE_TASK_STATUS_FILE = "delete from task_status where type=0 and dsId=?";
  private static final String SQL_DELETE_TASK_STATUS_VERSION = "delete from task_status where type=1 and dsId=? and major_version=? and minor_version=?";
  private static final String SQL_DELETE_BACKUP = "delete from task_backup where id=?";
  private static final String SQL_DELETE_BACKUP_DSID = "delete from task_backup where dsId=? or status=2";
  private static final String SQL_DELETE_RECOVERY = "delete from task_recovery where id=?";
  private static final String SQL_DELETE_VERSION_TREE = "delete from version_tree where ds_id=? and version=?";
  private static final String SQL_DELETE_VERSION_TREE_ALL = "delete from version_tree";

  private static Connection connection = null;

  public static void initConnection() throws SQLException {
    String db = FileUtil.getDatabase();
    if(connection == null){
      connection = DriverManager.getConnection("jdbc:sqlite:" + db);
    }
  }
  

  

  public static void initDatabase() throws SQLException {
    Statement stmt = connection.createStatement();
    stmt.execute(SQL_TABLE_BACKUP_TASK);
    stmt.execute(SQL_TABLE_RECOVERY_TASK);
    stmt.execute(SQL_TABLE_TASK_STATUS);
    stmt.execute(SQL_TABLE_VERSION_TREE);
    stmt.close();
  }
  
  public static void deleteVersion(String dsId,String version) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_VERSION_TREE);
      stmt.setString(1, dsId);
      stmt.setString(2, version);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  } 
  
  
  public static void deleteVersionAll() {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_VERSION_TREE_ALL);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }  
  
  public static List<DbVersionTreeBean> getVersionTreeNode(String dsId,String pid,String version) {
    List<DbVersionTreeBean> beans = new ArrayList<DbVersionTreeBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_VERSION_TREE);
      stmt.setString(1, dsId);
      stmt.setString(2, pid);
      stmt.setString(3, version);
      rs = stmt.executeQuery();
      while (rs.next()) {
        DbVersionTreeBean bean = new DbVersionTreeBean();
        String _id= rs.getString("id");
        String _pid= rs.getString("pid");
        String _dsId= rs.getString("ds_id");
        String _name= rs.getString("name");
        int _status= rs.getInt("status");
        String _version= rs.getString("version");
        bean.setId(_id);
        bean.setPid(_pid);
        bean.setDsId(_dsId);
        bean.setName(_name);
        bean.setStatus(_status);
        bean.setVersion(_version);
        beans.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return beans;
  }
  
  public static synchronized void addVersionTree(DbVersionTreeBean treeBean) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_VERSION_TREE);
      stmt.setString(1, treeBean.getId());
      stmt.setString(2,  treeBean.getPid());
      stmt.setString(3,  treeBean.getDsId());
      stmt.setString(4,  treeBean.getName());
      stmt.setInt(5, treeBean.getStatus());
      stmt.setString(6, treeBean.getVersion());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  public static synchronized void addVersionTreeMany(List<DbVersionTreeBean> treeBeans) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_VERSION_TREE);
      for (DbVersionTreeBean treeBean : treeBeans) {
        stmt.setString(1, treeBean.getId());
        stmt.setString(2,  treeBean.getPid());
        stmt.setString(3,  treeBean.getDsId());
        stmt.setString(4,  treeBean.getName());
        stmt.setInt(5, treeBean.getStatus());
        stmt.setString(6, treeBean.getVersion());
        stmt.addBatch();
      }
      connection.setAutoCommit(false);
      stmt.executeBatch();
      connection.setAutoCommit(true);
      
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  /**
   * @author 张建峰
   * @Description: 添加执行中的任务
   * @param @param taskStatus
   * @return void 
   * @date 2015年9月9日上午10:00:37
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void addTaskStatus(TaskStatusBean taskStatus) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_TASK_STATUS);
      stmt.setString(1, taskStatus.getDsId());
      stmt.setString(2,  taskStatus.getSignature());
      stmt.setInt(3,  taskStatus.getType());
      stmt.setInt(4,  taskStatus.getStatus());
      stmt.setString(5, taskStatus.getDsPath());
      stmt.setString(6, taskStatus.getInfoPath());
      stmt.setString(7, taskStatus.getInfoName());
      stmt.setLong(8, taskStatus.getFileSize());
      if(taskStatus.getVersion()!=null){
        stmt.setInt(9, taskStatus.getVersion().getMajorVersion());
        stmt.setInt(10, taskStatus.getVersion().getMinorVersion());
      }
      stmt.setInt(11, taskStatus.getDsType());
      stmt.setString(12, taskStatus.getIsEncrypt());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  public static synchronized void addTaskStatusMany(List<TaskStatusBean> taskStatusList) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_TASK_STATUS);
      for (TaskStatusBean taskStatus : taskStatusList) {
        
        stmt.setString(1, taskStatus.getDsId());
        stmt.setString(2,  taskStatus.getSignature());
        stmt.setInt(3,  taskStatus.getType());
        stmt.setInt(4,  taskStatus.getStatus());
        stmt.setString(5, taskStatus.getDsPath());
        stmt.setString(6, taskStatus.getInfoPath());
        stmt.setString(7, taskStatus.getInfoName());
        stmt.setLong(8, taskStatus.getFileSize());
        if(taskStatus.getVersion()!=null){
          stmt.setInt(9, taskStatus.getVersion().getMajorVersion());
          stmt.setInt(10, taskStatus.getVersion().getMinorVersion());
        }
        stmt.setInt(11, taskStatus.getDsType());
        stmt.setString(12, taskStatus.getIsEncrypt());
        
        stmt.addBatch();
      }
      connection.setAutoCommit(false);
      stmt.executeBatch();
      connection.setAutoCommit(true);
      
    } catch (SQLException e) {
      try {
        connection.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  /**
   * @author 张建峰
   * @Description: 获取为完成任务数量
   * @param @param type
   * @param @param dsId
   * @param @return
   * @return int 
   * @date 2015年9月9日上午10:00:53
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static int getTaskStatusFileUnfinishedCount(int type,String dsId) {
    int count = 0;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_COUNT_TASK_STATUS_UNFINISHED);
      stmt.setInt(1, type);
      stmt.setString(2, dsId);
      rs = stmt.executeQuery();
      while (rs.next()) {
        count = rs.getInt("count");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return count;
  }
  
  /**
   * @author 张建峰
   * @Description: 更新执行中文件任务
   * @param @param dsId
   * @param @param signature
   * @param @param status
   * @return void 
   * @date 2015年9月9日上午10:01:18
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized boolean updateTaskStatusFile(String dsId, String signature,int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_TASK_STATUS_FILE);
      stmt.setInt(1, status);
      stmt.setString(2, dsId);
      stmt.setString(3, signature);
      stmt.executeUpdate();
    } catch (SQLException e) {
      return false;
    } finally {
      close(stmt);
    }
    return true;
  }
  
  /**
   * @author 张建峰
   * @Description: 更新执行中版本任务
   * @param @param dsId
   * @param @param majorVersion
   * @param @param minorVersion
   * @param @param status
   * @return void 
   * @date 2015年9月9日上午10:01:32
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void updateTaskStatusVersion(String dsId,int majorVersion,int minorVersion, int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_TASK_STATUS_VERSION);
      stmt.setInt(1, status);
      stmt.setString(2, dsId);
      stmt.setInt(3, majorVersion);
      stmt.setInt(4, minorVersion);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  public static long getTaskStatusFileFinishedFileSizeSum(int _type,String dsId) {
    long count = 0;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_TASK_STATUS_FINISHED_FILESIZE_SUM);
      stmt.setInt(1, _type);
      stmt.setString(2, dsId);
      rs = stmt.executeQuery();
      while (rs.next()) {
        count = rs.getLong("count");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return count;
  }
  
  /**
   * @author 张建峰
   * @Description: 获取完成的任务
   * @param @param _type
   * @param @param dsId
   * @param @return
   * @return List<TaskStatusBean> 
   * @date 2015年9月9日上午9:58:27
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static List<TaskStatusBean> getTaskStatusFileFinished(int _type,String dsId) {
    List<TaskStatusBean> beans = new ArrayList<TaskStatusBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_TASK_STATUS_FINISHED);
      stmt.setInt(1, _type);
      stmt.setString(2, dsId);
      rs = stmt.executeQuery();
      while (rs.next()) {
        TaskStatusBean bean = new TaskStatusBean();
        Integer id = Integer.valueOf(rs.getString("id"));
        dsId = rs.getString("dsId");
        String signature = rs.getString("signature");
        Integer type = Integer.valueOf(rs.getString("type"));
        Integer status = Integer.valueOf(rs.getString("status"));
        String dsPath = rs.getString("ds_path");
        String infoPath = rs.getString("info_path");
        String infoName = rs.getString("info_name");
        Long fileSize = rs.getLong("file_size");
        Integer majorVersion = rs.getInt("major_version");
        Integer minorVersion = rs.getInt("minor_version");
        Integer dsType= rs.getInt("ds_type");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setType(type);
        bean.setStatus(status);
        bean.setDsPath(dsPath);
        bean.setInfoPath(infoPath);
        bean.setInfoName(infoName);
        bean.setFileSize(fileSize);
        DataSourceVersion version = new DataSourceVersion(majorVersion, minorVersion);
        bean.setVersion(version);
        bean.setDsType(dsType);
        bean.setIsEncrypt(isEncrypt);
        beans.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return beans;
  }
  
  /**
   * @author 张建峰
   * @Description: 获取为处理完成的任务
   * @param @param dsId
   * @param @return
   * @return List<TaskStatusBean> 
   * @date 2015年9月9日上午9:58:06
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static List<TaskStatusBean> getTaskStatusUnFinished(String dsId) {
    List<TaskStatusBean> beans = new ArrayList<TaskStatusBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_TASK_STATUS_UNFINISHED);
      stmt.setString(1, dsId);
      rs = stmt.executeQuery();
      while (rs.next()) {
        TaskStatusBean bean = new TaskStatusBean();
        Integer id = Integer.valueOf(rs.getString("id"));
        dsId = rs.getString("dsId");
        String signature = rs.getString("signature");
        Integer type = Integer.valueOf(rs.getString("type"));
        Integer status = Integer.valueOf(rs.getString("status"));
        String dsPath = rs.getString("ds_path");
        String infoPath = rs.getString("info_path");
        String infoName = rs.getString("info_name");
        Long fileSize = rs.getLong("file_size");
        Integer majorVersion = rs.getInt("major_version");
        Integer minorVersion = rs.getInt("minor_version");
        Integer dsType= rs.getInt("ds_type");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setType(type);
        bean.setStatus(status);
        bean.setDsPath(dsPath);
        bean.setInfoPath(infoPath);
        bean.setInfoName(infoName);
        bean.setFileSize(fileSize);
        DataSourceVersion version = new DataSourceVersion(majorVersion, minorVersion);
        bean.setVersion(version);
        bean.setDsType(dsType);
        bean.setIsEncrypt(isEncrypt);
        beans.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return beans;
  }
  
  public static List<TaskStatusBean> getTaskStatusBreakPoint(String dsId) {
    List<TaskStatusBean> beans = new ArrayList<TaskStatusBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_TASK_STATUS_BREAKPOINT);
      stmt.setString(1, dsId);
      rs = stmt.executeQuery();
      while (rs.next()) {
        TaskStatusBean bean = new TaskStatusBean();
        Integer id = Integer.valueOf(rs.getString("id"));
        dsId = rs.getString("dsId");
        String signature = rs.getString("signature");
        Integer type = Integer.valueOf(rs.getString("type"));
        Integer status = Integer.valueOf(rs.getString("status"));
        String dsPath = rs.getString("ds_path");
        String infoPath = rs.getString("info_path");
        String infoName = rs.getString("info_name");
        Long fileSize = rs.getLong("file_size");
        Integer majorVersion = rs.getInt("major_version");
        Integer minorVersion = rs.getInt("minor_version");
        Integer dsType= rs.getInt("ds_type");
        String isEncrypt = rs.getString("is_encrypt");
        bean.setId(id);
        bean.setDsId(dsId);
        bean.setSignature(signature);
        bean.setType(type);
        bean.setStatus(status);
        bean.setDsPath(dsPath);
        bean.setInfoPath(infoPath);
        bean.setInfoName(infoName);
        bean.setFileSize(fileSize);
        DataSourceVersion version = new DataSourceVersion(majorVersion, minorVersion);
        bean.setVersion(version);
        bean.setDsType(dsType);
        bean.setIsEncrypt(isEncrypt);
        beans.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return beans;
  }
  
  /**
   * @author 张建峰
   * @Description: 删除数据文件任务
   * @param @param dsId
   * @return void 
   * @date 2015年9月9日上午9:58:46
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void deleteTaskStatusFile(String dsId) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_TASK_STATUS_FILE);
      stmt.setString(1, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  /**
   * @author 张建峰
   * @Description: 删除版本任务
   * @param @param dsId
   * @param @param version
   * @return void 
   * @date 2015年9月9日上午9:58:59
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void deleteTaskStatusVersion(String dsId,DataSourceVersion version) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_TASK_STATUS_VERSION);
      stmt.setString(1, dsId);
      stmt.setInt(2, version.getMajorVersion());
      stmt.setInt(3, version.getMinorVersion());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }

  /**
   * @author 张建峰
   * @Description: 添加备份任务
   * @param @param backup
   * @return void 
   * @date 2015年9月9日上午9:59:12
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void addBackupTask(BackupBean backup) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_BACKUP);
      stmt.setString(1, backup.getDsId());
      stmt.setInt(2,  backup.getStatus());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }

  /**
   * @author 张建峰
   * @Description: 添加恢复任务
   * @param @param recovery
   * @return void 
   * @date 2015年9月9日上午9:59:20
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void addRecoveryTask(RecoveryBean recovery) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_RECOVERY);
      stmt.setString(1, recovery.getDsId());
      stmt.setString(2, recovery.getPath());
      stmt.setString(3, recovery.getVersion());
      stmt.setInt(4, recovery.getStatus());
      stmt.setString(5, recovery.getRecoveryPath());
      stmt.setString(6, recovery.getNid());
      stmt.setString(7, recovery.getUserClickNodePath());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }

  /**
   * @author 张建峰
   * @Description: 删除备份任务
   * @param @param id
   * @return void 
   * @date 2015年9月9日上午9:59:29
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void deleteBackupTask(int id) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_BACKUP);
      stmt.setInt(1, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  /**
   * @author zhangjf
   * @Description: 根据Dsid删除备份记录并且删除备份成功的记录
   * @param @param id
   * @return void 
   * @date 2015年10月29日09:37:59
   *
   * @修改人 zhangjf
   * @修改日期
   */
  public static synchronized void deleteBackupTaskByDsid(String dsid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_BACKUP_DSID);
      stmt.setString(1, dsid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }

  /**
   * @author 张建峰
   * @Description: 删除恢复任务
   * @param @param id
   * @return void 
   * @date 2015年9月9日上午9:59:39
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void deleteRecoveryTaskMany(String ids) {
    PreparedStatement stmt = null;
    String[] idArr = ids.split(",");
    try {
      stmt = connection.prepareStatement(SQL_DELETE_RECOVERY);
      for (String id : idArr) {
        stmt.setInt(1, Integer.valueOf(id));
        stmt.addBatch();
      }
      connection.setAutoCommit(false);
      stmt.executeBatch();
      connection.setAutoCommit(true);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }
  
  public static synchronized void deleteRecoveryTask(int id) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_RECOVERY);
      stmt.setInt(1, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }

  /**
   * @author 张建峰
   * @Description: 更新备份任务
   * @param @param id
   * @param @param status
   * @return void 
   * @date 2015年9月9日上午9:59:47
   *
   * @修改人 张建峰'
   * 
   * @修改日期
   */
  public static synchronized void updateBackupTask(int id, int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_BACKUP);
      stmt.setInt(1, status);
      stmt.setInt(2, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }

  }

  /**
   * @author 张建峰
   * @Description: 更新恢复任务
   * @param @param id
   * @param @param status
   * @return void 
   * @date 2015年9月9日上午10:00:04
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static synchronized void updateRecoveryTask(int id, int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_RECOVERY);
      stmt.setInt(1, status);
      stmt.setInt(2, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, null);
    }

  }

  /**
   * @author 张建峰
   * @Description: 获取备份任务
   * @param @return
   * @return List<BackupBean> 
   * @date 2015年9月9日上午10:00:13
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static  List<BackupBean> getBackupTasks() {
    List<BackupBean> list = new ArrayList<BackupBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_BACKUP);
      rs = stmt.executeQuery();
      while (rs.next()) {
        int id = rs.getInt("id");
        String dsId = rs.getString("dsId");
        BackupBean bb = new BackupBean();
        bb.setDsId(dsId);
        bb.setId(id);
        list.add(bb);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return list;
  }

  /**
   * @author 张建峰
   * @Description: 获取恢复任务
   * @param @return
   * @return List<RecoveryBean> 
   * @date 2015年9月9日上午10:00:21
   *
   * @修改人 张建峰
   * @修改日期
   */
  public static List<RecoveryBean> getRecoveryTasks() {
    List<RecoveryBean> list = new ArrayList<RecoveryBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_RECOVERY);
      rs = stmt.executeQuery();
      while (rs.next()) {
        int id = rs.getInt("id");
        String dsId = rs.getString("dsId");
        String path = rs.getString("path");
        String version = rs.getString("version");
        String recoveryPath = rs.getString("recoveryPath");
        Integer status = rs.getInt("status");
        RecoveryBean rb = new RecoveryBean(id, dsId, path, version);
        rb.setStatus(status);
        rb.setRecoveryPath(recoveryPath);
        rb.setNid(rs.getString("nid"));
        rb.setUserClickNodePath(rs.getString("userClickNodePath"));
        list.add(rb);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return list;
  }
  
  
  public static List<RecoveryBean> getRunningRecoveryTasks() {
      List<RecoveryBean> list = new ArrayList<RecoveryBean>();
      PreparedStatement stmt = null;
      ResultSet rs = null;
      try {
        stmt = connection.prepareStatement(SQL_SELECT_RECOVERY_Running);
        rs = stmt.executeQuery();
        while (rs.next()) {
          int id = rs.getInt("id");
          String dsId = rs.getString("dsId");
          String path = rs.getString("path");
          String version = rs.getString("version");
          String recoveryPath = rs.getString("recoveryPath");
          Integer status = rs.getInt("status");
          RecoveryBean rb = new RecoveryBean(id, dsId, path, version);
          rb.setStatus(status);
          rb.setRecoveryPath(recoveryPath);
          rb.setNid(rs.getString("nid"));
          rb.setUserClickNodePath(rs.getString("userClickNodePath"));
          list.add(rb);
        }
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        close(stmt, rs);
      }

      return list;
    }
  
  
  public static List<RecoveryBean> getRecoveryTasksAll() {
    List<RecoveryBean> list = new ArrayList<RecoveryBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_RECOVERY_ALL);
      rs = stmt.executeQuery();
      while (rs.next()) {
        int id = rs.getInt("id");
        String dsId = rs.getString("dsId");
        String path = rs.getString("path");
        String version = rs.getString("version");
        String recoveryPath = rs.getString("recoveryPath");
        Integer status = rs.getInt("status");
        RecoveryBean rb = new RecoveryBean(id, dsId, path, version);
        rb.setStatus(status);
        rb.setNid(rs.getString("nid"));
        rb.setRecoveryPath(recoveryPath);
        rb.setUserClickNodePath(rs.getString("userClickNodePath"));
        list.add(rb);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return list;
  }
  
  public static int getRecoveryCount() {
    int count = 0;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_COUNT_RECOVERY_ALL);
      rs = stmt.executeQuery();
      while (rs.next()) {
        count = rs.getInt("count");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return count;
  }
  
  public static List<RecoveryBean> getRecoveryTasksByPage(int offset,int pagesize) {
    List<RecoveryBean> list = new ArrayList<RecoveryBean>();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_RECOVERY_PAGE);
      stmt.setInt(1, pagesize);
      stmt.setInt(2, offset);
      rs = stmt.executeQuery();
      while (rs.next()) {
        int id = rs.getInt("id");
        String dsId = rs.getString("dsId");
        String path = rs.getString("path");
        String version = rs.getString("version");
        String recoveryPath = rs.getString("recoveryPath");
        Integer status = rs.getInt("status");
        RecoveryBean rb = new RecoveryBean(id, dsId, path, version);
        rb.setStatus(status);
        rb.setRecoveryPath(recoveryPath);
        rb.setNid(rs.getString("nid"));
        rb.setUserClickNodePath(rs.getString("userClickNodePath"));
        list.add(rb);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }

    return list;
  }

  public static void close(PreparedStatement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(PreparedStatement stmt) {
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close() {

    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }


}
